package app.beit.fintrack.dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import app.beit.fintrack.entity.summary.SummaryTrackPidData;
import app.beit.fintrack.utils.DateFormatUtils;
import app.beit.fintrack.utils.enums.ESearchByStatus;

public class SummaryPIDDao extends BaseDao {

	private final Logger log = Logger.getLogger(this.getClass());

	@SuppressWarnings("unchecked")
	public List<SummaryTrackPidData> getDetailsByPID(final String hospitalCode, final String type, final String code, final Date dateFromAdmidTime,
			final Date dateToAdmidTime, final int start, final int end) throws Exception {
		this.log.info("get details data from code : " + code + " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		
		sb.append("select rownum as id, ms.* from ( ");
		sb.append("select ms.*, s.auth_type as authType, s.status as statusType ");
		sb.append("from (select s.*, dw.run_dt as paymentDate, dw.adtl_id as batchId, dw.dr_cr_idct as expression ");
		sb.append("from (select sss.*, to_number(replace(m.sums_serviceitem, ',', '')) as oldDischarge, ");
		sb.append("case when (m.code_id is null or m.code_id is not null) and (m.status=9 or m.status=4) then  ");
		sb.append("nvl((select sum(act_amt) from m_sum_subfund where tran_id=sss.parentTranId AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0) ");
		sb.append("   else 0 ");
		sb.append("end as oldCompensation, ");
		sb.append("m.code_dw_hc as oldCodeDWHC, m.code_dw_inst as oldCodeDWINST, m.code_dw_ae as oldCodeDWAE, m.code_dw_ip as oldCodeDWIP,");
		sb.append("m.code_dw_dmis as oldCodeDWDMIS, m.code_dw_ip_cslg as oldCodeDWIPCSLG, m.code_dw_op_cslg as oldCodeDWOPCSLG, m.code_dw_pa_cslg as oldCodeDWPACSLG, ");
		sb.append("m.code_dw_inst_cslg as oldCodeDWINSTCSLG, m.code_dw_ot_cslg as oldCodeDWOTCSLG, ");
		sb.append("case when m.code_id is null and m.code_dw_hc='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as oldSumsHC, ");
		sb.append("case when m.code_id is null and m.code_dw_ae='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPNB','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP3SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP7SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as oldSumsAE, ");
		sb.append("case when m.code_id is null and m.code_dw_inst='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'INST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as oldSumsINST, ");
		sb.append("case when m.code_id is null and m.code_dw_ip='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAEC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAER','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINSPSN','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))");
		sb.append("else null end as oldSumsIP, ");
		sb.append("case when m.code_id is null and m.code_dw_dmis='C' then  ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATARACT','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMISRC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'DMISRC')), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSC')), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSR')), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLOP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'STROKE-DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMIDML','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'FPNHSO','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as oldSumsDMIS, ");
		sb.append("is_number(FN_SUMS_SUBFUND(m.TRAN_ID,'DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)) as oldSumsDRUG, ");
		sb.append("case when m.code_id is null and m.code_dw_ip_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'IP')), 0)) ");
		sb.append("else null end as oldSumsIPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_op_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OP')), 0)) ");
		sb.append("else null end as oldSumsOPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_pa_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'PA')), 0)) ");
		sb.append("else null end as oldSumsPACS, ");
		sb.append("case when m.code_id is null and m.code_dw_inst_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'INST')), 0))");
		sb.append("else null end as oldSumsINSTCS, ");
		sb.append("case when m.code_id is null and m.code_dw_ot_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OT')), 0))");
		sb.append("else null end as oldSumsOTCS, ");
		sb.append("is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'DRUG'))as oldSumsDRUGCS ");
		sb.append(" from ");
		sb.append("(select rownum as rid, m.tran_id as tranId, m.parent_tran_id as parentTranId, m.eclaim_no as eclaimNo, a.hosp_rep_id as repno, ");
		sb.append("m.pid as pid, m.hn as hn, m.an as an, concat(concat(m.fname, ' '), m.lname) as fullname, to_date(m.dateadm, 'yyyyMMdd') as admidDate, ");
		sb.append("m.timeadm as admidtime, to_date(m.datedsc, 'yyyyMMdd') as dischargedate, m.timedsc as dischargetime, ");
		sb.append("case when m.person_type is not null then ");
		sb.append(" case when m.person_type='1' then 'OP' ");
		sb.append(" when m.person_type='2' then 'IP' end ");
		sb.append("else ");
		sb.append(" case when (m.hn is not null and m.hn<>'-') and (m.an is null or m.an<>'-') then 'OP' ");
		sb.append(" when (m.hn is null or m.hn<>'-') and (m.an is not null and m.an<>'-') then 'IP' ");
		sb.append(" when (m.hn is not null and m.hn<>'-') and (m.an is not null and m.an<>'-') then 'IP' end ");
		sb.append("end as personType, ");
		sb.append("to_number(replace(m.sums_serviceitem, ',', '')) as discharge, ");
		sb.append("case when (m.code_id is null or m.code_id is not null) and (m.status=9 or m.status=4) then  ");
		sb.append("nvl((select sum(act_amt) from m_sum_subfund where m.tran_id=tran_id AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0) ");
		sb.append("   else 0 ");
		sb.append("end as compensation, ");
		sb.append("case when fn_rate(tran_id)<>'-' then to_char(fn_rate(tran_id)*100) ");
		sb.append(" else '-'  end as salarypercent, ");
        sb.append("FN_SUMS_SALARY(tran_id) as salaryamount, fn_sums_charge_rule(tran_id) as chargeRule, ");
		sb.append("case when m.filename_send like '%ORF%' then 'Y' else '' end as orf, ");
		sb.append("case when m.filename_send like '%APPEAL%' then 'A' ");
		sb.append("else");
		sb.append(" case when (m.code_id is null or m.code_id is not null) and (m.status=9 or m.status=4) then 'P' ");
		sb.append(" when m.code_id='DENY' then 'ND' ");
		sb.append(" else 'N' ");
		sb.append("end end as status, ");
		sb.append("to_number(replace(fn_sums_oprefer_hmain(m.tran_id), ',', '')) as orfHMain, ");
		sb.append("to_number(replace(fn_sums_oprefer_nhso(m.tran_id), ',', '')) as orfNHSO, ");
		sb.append("a.hospital_id as hospitalCode, fn_get_hosp_name(a.hospital_id) as hospitalName, m.code_id as codeId, ");
		sb.append("m.code_dw_hc as codeDWHC, m.code_dw_inst as codeDWINST, m.code_dw_ae as codeDWAE, m.code_dw_ip as codeDWIP, m.code_dw_dmis as codeDWDMIS,");
		sb.append("m.code_dw_ip_cslg as codeDWIPCSLG, m.code_dw_op_cslg as codeDWOPCSLG, m.code_dw_pa_cslg as codeDWPACSLG, m.code_dw_inst_cslg as codeDWINSTCSLG, ");
		sb.append("m.code_dw_ot_cslg as codeDWOTCSLG, ");
		sb.append("case when m.code_id is null and m.code_dw_hc='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as sumsHC, ");
		sb.append("case when m.code_id is null and m.code_dw_ae='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'OPAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPNB','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPPUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IP3SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IP7SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'CARAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'CARREF','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'CARREF-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as sumsAE, ");
		sb.append("case when m.code_id is null and m.code_dw_inst='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'OPINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'INST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as sumsINST, ");
		sb.append("case when m.code_id is null and m.code_dw_ip='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPAEC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPAER','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPINRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPINRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPINSPSN','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPPRCC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'IPPRCC-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as sumsIP, ");
		sb.append("case when m.code_id is null and m.code_dw_dmis='C' then  ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'CATARACT','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'CATINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'DMISRC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(m.tran_id,'DMISRC')), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'RCUHOSC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(m.tran_id,'RCUHOSC')), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'RCUHOSR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(m.tran_id,'RCUHOSR')), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'LLOP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'LLRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'LLRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'LP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'STROKE-DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'DMIDML','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND(m.tran_id,'FPNHSO','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append("else null end as sumsDMIS, ");
		sb.append("is_number(FN_SUMS_SUBFUND(m.tran_id,'DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)) as sumsDRUG, ");
		sb.append("case when m.code_id is null and m.code_dw_ip_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'IP')), 0)) ");
		sb.append("else null end as sumsIPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_op_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'OP')), 0)) ");
		sb.append("else null end as sumsOPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_pa_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'PA')), 0)) ");
		sb.append("else null end as sumsPACS, ");
		sb.append("case when m.code_id is null and m.code_dw_inst_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'INST')), 0)) ");
		sb.append("else null end as sumsINSTCS, ");
		sb.append("case when m.code_id is null and m.code_dw_ot_cslg='C' then ");
		sb.append(" to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'OT')), 0))");
		sb.append("else null end as sumsOTCS, ");
		sb.append("is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'DRUG'))as sumsDRUGCS ");
		sb.append("from m_upload a inner join m_register m on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("where m." + type).append("='").append(code).append("' ");
		sb.append("and m.date_send>=to_date('01052013', 'ddMMyyyy') ");
		if (hospitalCode != null && hospitalCode.length() > 0) {
			sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		}

		if (dateFromAdmidTime != null) {
			sb.append("and to_date(m.DATEADM, 'yyyyMMdd') >= to_date('").append(DateFormatUtils.DateThaiToDate(dateFromAdmidTime)).append("', 'yyyyMMdd') ");
		}
		if (dateToAdmidTime != null) {
			sb.append("and to_date(m.DATEADM, 'yyyyMMdd') <= to_date('").append(DateFormatUtils.DateThaiToDate(dateToAdmidTime)).append("', 'yyyyMMdd') ");
		}
		sb.append(") sss left join m_register m on m.tran_id=sss.parentTranId ");
		sb.append("where sss.rid>").append(start).append(" and sss.rid<=").append(end).append(") s ");
		sb.append("left join ods_dw_regup dw on dw.ref_doc_no=s.repNo and substr(dw.vndr_no, 6)=s.hospitalCode ");
		sb.append("and dw.idct_prop_run<>'X' and dw.pmnt_doc_no<>' ') ms left join m_seamless s on ms.repno=s.repno and s.hospitalcode=ms.hospitalcode ");
		sb.append("group by ms.rid, TRANID, PARENTTRANID, ECLAIMNO, ms.REPNO, PID, HN, AN, FULLNAME, ADMIDDATE, ADMIDTIME, DISCHARGEDATE, DISCHARGETIME, ");
		sb.append("PERSONTYPE, DISCHARGE, COMPENSATION, SALARYPERCENT, SALARYAMOUNT, CHARGERULE, ORF, ms.STATUS, ORFHMAIN, ORFNHSO, ms.HOSPITALCODE, ");
		sb.append("HOSPITALNAME, CODEID, CODEDWHC, CODEDWINST, CODEDWAE, CODEDWIP, CODEDWDMIS, CODEDWIPCSLG, CODEDWOPCSLG, CODEDWPACSLG, CODEDWINSTCSLG, ");
		sb.append("CODEDWOTCSLG, SUMSHC, SUMSAE, SUMSINST, SUMSIP, SUMSDMIS, SUMSDRUG, SUMSIPCS, SUMSOPCS, SUMSPACS, SUMSINSTCS, SUMSOTCS, SUMSDRUGCS, ");
		sb.append("OLDDISCHARGE, OLDCOMPENSATION, OLDCODEDWHC, OLDCODEDWINST, OLDCODEDWAE, OLDCODEDWIP, OLDCODEDWDMIS, OLDCODEDWIPCSLG, OLDCODEDWOPCSLG, ");
		sb.append("OLDCODEDWPACSLG, OLDCODEDWINSTCSLG, OLDCODEDWOTCSLG, OLDSUMSHC, OLDSUMSAE, OLDSUMSINST, OLDSUMSIP, OLDSUMSDMIS, OLDSUMSDRUG, ");
		sb.append("OLDSUMSIPCS, OLDSUMSOPCS, OLDSUMSPACS, OLDSUMSINSTCS, OLDSUMSOTCS, OLDSUMSDRUGCS, ");
		sb.append("paymentDate, batchId, expression, s.auth_type, s.status ");
		sb.append("order by admiddate desc) ms ");
		this.log.info("get details data from repno with sql query : "+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackPidData.class);
		final List<SummaryTrackPidData> results = sql.list();
		session.close();
		this.log.info("end get details data from repno..........");

		return results;
	}
	
	public BigDecimal getCountPerson(final String hospitalCode, final String type, final String code, final Date dateFromAdmidTime,
			final Date dateToAdmidTime, final ESearchByStatus status) throws Exception {
		this.log.info("count all person from details data ");
		final StringBuilder sb = new StringBuilder();
		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename ");
		sb.append("and m.hcode=a.hospital_id ");
		sb.append("where m." + type).append("='").append(code).append("' ");
		sb.append("and m.date_send>=to_date('01052013', 'ddMMyyyy') ");
		sb.append("and a.hosp_rep_id is not null ");

		if (hospitalCode != null && hospitalCode.length() > 0) {
			sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		}
		if (dateFromAdmidTime != null) {
			sb.append("and to_date(m.DATEADM, 'yyyyMMdd') >= to_date('").append(DateFormatUtils.DateThaiToDate(dateFromAdmidTime)).append("', 'yyyyMMdd') ");
		}
		if (dateToAdmidTime != null) {
			sb.append("and to_date(m.DATEADM, 'yyyyMMdd') <= to_date('").append(DateFormatUtils.DateThaiToDate(dateToAdmidTime)).append("', 'yyyyMMdd') ");
		}
		
		if(ESearchByStatus.APPEAL == status){
			sb.append("and m.filename_send like '%APPEAL%' ");
		}else if(ESearchByStatus.NOT_PASS == status){
			sb.append("and m.code_id<>'DENY' and m.code_id is not null and m.status not in('9', '4') ");
		}else if(ESearchByStatus.NOT_PASS_C == status){
			sb.append("and m.code_id='DENY' ");
		}else if(ESearchByStatus.PASS == status){
			sb.append("and (m.code_id is null ");
			sb.append("or m.code_id is not null) and m.status in ('9', '4') ");
			sb.append("and m.filename_send not like '%APPEAL%' ");
		}
		this.log.info("sql count all person from details data : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end count all person from details data...... ");
		
		return result;
	}
}
